USE ConsultTracking
GO
drop proc CreateNote
drop proc UpdateNote
GO
------------------------------------------------------------------
create proc CreateNote
@TaskID int,
@UserID int,
@Description nvarchar(1000),
@NoteID int output,
@Created smalldatetime output,
@Modified smalldatetime output
as
INSERT INTO [ConsultTracking].[dbo].[Note]
           ([TaskID]
           ,[UserID]
           ,[Description])
     VALUES
           (@TaskID
           ,@UserID
           ,@Description)

select @NoteID = NoteID, @Created = Created, @Modified = Modified
from Note
where NoteID = Scope_Identity()
---------------------------------------------------------------
go
create proc UpdateNote
@NoteID int,
@UserID int,
@Description nvarchar(1000),
@Modified smalldatetime
as

declare @NoteCreator int
set @NoteCreator = (select top(1) UserID from Note where NoteID = @NoteID)

if @UserID = @NoteCreator
begin
	UPDATE [ConsultTracking].[dbo].[Note]
	   SET [Description] = @Description,
		[Modified] = GETDATE()
	 WHERE NoteID = @NoteID and Modified = @Modified
end

select @Modified = Modified 
from Note
where NoteID = @NoteID